package com.sihai.ehr.service.impl;

import com.sihai.core.DataAccessor;
import com.sihai.ehr.model.biz.*;
import com.sihai.ehr.model.sys.Organization;
import com.sihai.ehr.service.DataImportService;
import com.sihai.ehr.utils.DateUtil;
import com.sihai.ehr.utils.ExcelUtils;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.*;
import jxl.format.Colour;
import jxl.write.*;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.commons.lang.xwork.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author: wxn
 * @since: 12-7-19 下午22:53
 *
 * 数据导入service
 */
@Service("dataImportService")
public class DataImportServiceImpl implements DataImportService{
    @Resource
    private DataAccessor dataAccessor;

    public String importEmployees(File file){
        if(file == null){
            return "file_not_found";
        }
        List<String[]> rows = ExcelUtils.readExcel(file, 0, 0);
        if(rows == null || rows.isEmpty()){
            return "file_is_empty";
        }

        int count = 0;
        for(String[] row : rows){
            if(row == null || row.length != 35){
                continue;
            }
            try{
                Date birthday = DateUtil.parseDate(row[7]);
                Integer workAge = NumberUtils.createInteger(row[33]);
                EmployeeinfoBase employee = new EmployeeinfoBase(
                        row[0],row[1],row[2],row[3],row[4],row[5],row[6],birthday,row[8],row[9],
                        row[10],row[11],row[12],row[13],row[14],row[15],row[16],row[17],row[18],row[19],
                        row[20],row[21],row[22],row[23],row[24],row[25],row[26],row[27],row[28],row[29],
                        row[30],row[31],row[32],workAge, row[34]);
                employee.setCancel("0");
                dataAccessor.save(employee);
                count++;
            }catch (Exception e){
                e.printStackTrace();
            }
        }

        if(count == rows.size()){
            return "all_success";
        }else{
            return Integer.toString(count);
        }
    }

    //add.wxn:2012.11,15
    //员工数据批量导入(见模板employee_info)
    public String importEmployeesForAll(File file){
        if(file == null){
            return "file_not_found";
        }
        List<String[]> rows = ExcelUtils.readExcelUsePoi(file, 0, 3);
        if(rows == null || rows.isEmpty()){
            return "file_is_empty";
        }

        List<ImportErrorInfo> errorList = new ArrayList<ImportErrorInfo>();
        List<Grade> grades = dataAccessor.findByHql(Grade.class, "from Grade g where g.cancel <> 1", null, null, null);

        int index = 0;
        for(String[] row : rows){
            try{
                index++;
                if(grades == null || grades.isEmpty()){
                    errorList.add(new ImportErrorInfo(index, "找不到对应的职等或职级", row));
                    continue;
                }
                Grade grade = null;
                for(Grade g : grades){
                    if(StringUtils.equals(g.getGradeName(), row[5]) && StringUtils.equals(g.getGradeSort(), row[6])){
                        Duty d = g.getDuty(); //职务
                        if(d == null || !StringUtils.equals(d.getDutyName(), row[4])){
                            continue;
                        }
                        Organization o = d.getOrganization(); //部门
                        if(o == null || !StringUtils.equals(o.getFullName(), row[3])){
                            continue;
                        }
                        Organization t = o.getTopOrganization(); //分公司
                        if(t == null || !StringUtils.equals(t.getFullName(), row[2])){
                            continue;
                        }
                        grade = g;
                        break;
                    }
                }

                if(grade == null){
                    errorList.add(new ImportErrorInfo(index, "分公司、部门、职务、职级、职等不对应", row));
                    continue;
                }

                String workNo = row[1]; //工号
                if(StringUtils.isBlank(workNo)){
                    errorList.add(new ImportErrorInfo(index, "工号不为空", row));
                    continue;
                }
                List<Long> temps = dataAccessor.findByHql(Long.class, "select w.id from EmployeeinfoWork w where w.duty.id=? and w.workNo=?", new Object[]{grade.getDuty().getId(), workNo}, null, null);
                if(temps != null && !temps.isEmpty()){
                    errorList.add(new ImportErrorInfo(index, "工号重复", row));
                    continue;
                }


                String salaryNo = row[0]; //考勤工号
                if(StringUtils.isBlank(salaryNo)){
                    errorList.add(new ImportErrorInfo(index, "考勤工号不为空", row));
                    continue;
                }
                List<Long> employees = dataAccessor.findByHql(Long.class, "select w.id from EmployeeinfoWork w where w.baseId.salaryNo=? and w.baseId.state='1'", new Object[]{salaryNo}, null, null);
                if(employees != null && !employees.isEmpty()){
                    errorList.add(new ImportErrorInfo(index, "考勤工号工号重复", row));
                    continue;
                }


                EmployeeinfoBase base = new EmployeeinfoBase();
                base.setName(row[8]);
                base.setFacePath(row[9]);
                base.setSex(StringUtils.endsWithIgnoreCase(row[10], "男") ? "0" : "1");
                base.setTel(row[11]);
                base.setHandSet(row[12]);
                base.setAddress(row[13]);
                base.setPostcode(row[14]);
                base.setIdcard(row[15]);
                base.setBirthday(DateUtil.createDate(row[16]));
                base.setFolk(row[17]);
                base.setCensus(row[18]);
                base.setArchives(row[19]);
                base.setStature(row[20]);
                base.setAvoirdupois(row[21]);
                base.setEyeLeft(row[22]);
                base.setEyeRight(row[23]);
                base.setEmail(row[24]);
                base.setMarry(StringUtils.endsWithIgnoreCase(row[25], "是") ? "0" : "1");
                base.setBaby(StringUtils.endsWithIgnoreCase(row[26], "是") ? "0" : "1");
                base.setCompensation(row[27]);
                base.setCertificate(getCertificateValue(row[28]));//学历
                base.setCertificate(getTechValue(row[29]));
                base.setEntryDate(DateUtil.formatDate(row[30]));
                base.setEnglishRead(getLevelValue(row[31]));
                base.setEnglishWrite(getLevelValue(row[32]));
                base.setEnglishDialog(getLevelValue(row[33]));
                base.setEnglishLetter(getEnglishValue(row[34]));
                base.setLanguageRead(getLevelValue(row[35]));
                base.setLanguageWrite(getLevelValue(row[36])) ;
                base.setLanguageDialog(getLevelValue(row[37]));
                base.setRemark(row[38]);
                base.setCancel("0");
                base.setState("1"); //在职员工

                //教育
                EmployeeinfoEducation education = new EmployeeinfoEducation();
                education.setSchoolName(row[40]);
                education.setSpecialty(row[41]);
                education.setDegree(row[42]);
                education.setBeginDate(DateUtil.createDate(row[43]));
                education.setEndDate(DateUtil.createDate(row[44]));
                education.setBaseId(base);

                //培训
                EmployeeinfoTrain train = new EmployeeinfoTrain();
                train.setLetter(row[46]);
                train.setTrainAddress(row[47]);
                train.setTrainContent(row[48]);
                train.setBeginDate(DateUtil.createDate(row[49]));
                train.setEndDate(DateUtil.createDate(row[50]));
                train.setBaseId(base);

                //工作经历
                EmployeeWorkExperience experience = new EmployeeWorkExperience();
                experience.setCompname(row[52]);
                experience.setJob(row[53]);
                experience.setMonthlyPay(row[54]);
                experience.setProveMan(row[55]);
                experience.setContel(row[56]);
                experience.setBeginDate(DateUtil.createDate(row[57]));
                experience.setEndDate(DateUtil.createDate(row[58]));
                experience.setResignReason(row[59]);
                experience.setEmployeeinfoBase(base);

                EmployeeinfoFamily family = new EmployeeinfoFamily();
                family.setFamilyRelation(row[61]);
                family.setCompname(row[62]);
                family.setAddress(row[63]);
                family.setName(row[64]);
                family.setContel(row[65]);
                family.setExigenceConname(row[66]);
                family.setExigenceRelation(row[67]);
                family.setEmployeeinfoBase(base);

                EmployeeinfoWork work = new EmployeeinfoWork();
                work.setDuty(grade.getDuty());
                work.setOrganization(grade.getDuty().getOrganization());
                work.setGrade(grade);
                work.setGradeLevel(grade);
                work.setWorkNo(workNo);
                work.setBaseId(base);
                work.setCancel("0");

                dataAccessor.save(base);
                dataAccessor.save(work);
                dataAccessor.save(education);
                dataAccessor.save(train);
                dataAccessor.save(experience);
                dataAccessor.save(family);

            }catch (Exception e){
                e.printStackTrace();
            }
        }

        if(errorList.isEmpty()){
            return "success";
        }

        writeInfosUsePoi(file, 0, 3, errorList);

        StringBuffer sb = new StringBuffer();
        for(ImportErrorInfo info : errorList){
            sb.append("第").append(info.getIndex()).append("行导入出错：").append(info.getMessage()).append("\n");
        }
        sb.append("详情请查看tomcat/webapps/ehr目录下的'员工基本数据导入报告.xls'文件");
        return sb.toString();
    }

    private String[] mCertificates = {"小学","初中","高中","大专","本科","硕士","博士","博士后"};
    private String getCertificateValue(String certificate){
        for(int i=0; i<mCertificates.length; i++){
            if(StringUtils.equalsIgnoreCase(certificate, mCertificates[i])){
                return String.valueOf(i+1);
            }
        }
        return "";
    }

    private String[] mTechs = {"多能工","傅级","熟手","半熟手","时装熟手","一般","生手"};
    private String getTechValue(String tech){
        for(int i=0; i<mTechs.length; i++){
            if(StringUtils.equalsIgnoreCase(tech, mTechs[i])){
                return String.valueOf(i+1);
            }
        }
        return "";
    }

    private String[] mLevel = {"一般","熟练","精通"};
    private String getLevelValue(String level){
        for(int i=0; i<mLevel.length; i++){
            if(StringUtils.equalsIgnoreCase(level, mLevel[i])){
                return String.valueOf(i+1);
            }
        }
        return "";
    }

    private String[] mEnglish = {"三级","四级","六级","八级"};
    private String getEnglishValue(String english){
        for(int i=0; i<mEnglish.length; i++){
            if(StringUtils.equalsIgnoreCase(english, mEnglish[i])){
                return String.valueOf(i+1);
            }
        }
        return "";
    }

    //jxl写入导入报告(window平台下tomcat服务器会报OOM错误)
    private void writeInfosUseJxl(File file, int sheet, int offsetRow, List<ImportErrorInfo> infos){
        if(infos == null || infos.isEmpty()){
            return;
        }

        try {
            Workbook workbook = Workbook.getWorkbook(file);

            // Workbook 对象是只读的，所以如果要修改Excel，需要创建一个可读的副本，副本指向原Excel文件（即下面的new File(excelpath)）
            //WritableWorkbook writableWorkBook = Workbook.createWorkbook(file, workbook);//创建workbook的副本(副本指向原本)
            //WritableWorkbook writableWorkBook = Workbook.createWorkbook(new File(String.format("%s/员工基本数据导入报告.xls", file.getParent())), workbook);//创建workbook的副本
            String path = String.format("%s/员工基本数据导入报告.xls", file.getParent());
            WritableWorkbook writableWorkBook = Workbook.createWorkbook(new File(path), workbook);//创建workbook的副本
            WritableSheet writableSheet = writableWorkBook.getSheet(sheet);//此处只读取第一个sheet内容

            WritableFont font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false);
            font.setColour(jxl.format.Colour.RED);
            WritableCellFormat format = new WritableCellFormat(font);
            for(ImportErrorInfo info : infos){
                //WritableCell cell = writableSheet.getWritableCell(70, rowNum+i);//获取第一个单元格
                Label label = new Label(69, offsetRow+info.getIndex()-1, info.getMessage(), format);
                writableSheet.addCell(label);
            }

            writableWorkBook.write();
            writableWorkBook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //poi写入导入报告
    private void writeInfosUsePoi(File file, int sheet, int offsetRow, List<ImportErrorInfo> infos){
        HSSFWorkbook workBook = null;
        try {
            //读取模板Excel
            workBook = new HSSFWorkbook(new FileInputStream(file));
            //workBook.createSheet("employee_info").createRow(30).createCell(2, Cell.CELL_TYPE_STRING).setCellValue("anle102你好");
            HSSFSheet hssfSheet = workBook.getSheet("employee_info");

            HSSFFont font = workBook.createFont();
            font.setColor(HSSFColor.RED.index);
            HSSFCellStyle hssfCellStyle = workBook.createCellStyle();
            hssfCellStyle.setFont(font);

            for(ImportErrorInfo info : infos){
                HSSFCell hssfCell = hssfSheet.getRow(offsetRow + info.getIndex() - 1).createCell(69, org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
                hssfCell.setCellStyle(hssfCellStyle);
                hssfCell.setCellValue(info.getMessage());
            }
            String path = String.format("%s/员工基本数据导入报告.xls", file.getParent());
            FileOutputStream out = new FileOutputStream(path);
            // 把相应的Excel 工作簿存盘
            workBook.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private class ImportErrorInfo{
        private int index;
        private String message;
        private String[] data;

        public ImportErrorInfo(){}
        public ImportErrorInfo(int index, String message, String[] data) {
            this.index = index;
            this.message = message;
        }

        public int getIndex() {
            return index;
        }

        public String getMessage() {
            return message;
        }

        public String[] getData() {
            return data;
        }

        public void setData(String[] data) {
            this.data = data;
        }
    }

}
